package dataAccess;

import businessEntities.ColumnsInformationContainer;
import businessEntities.DBInformationContainer;
import businessEntities.MainLogger;
import businessEntities.exceptions.ColumnsQueryException;
import businessEntities.exceptions.InformationQueryException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DALInformationQuery {

    /**
     * Return DBInformationContainer - result of DataBase query on information_schema.TABLES
     * @throws ConnectionException if the connection to the database is not reachable.
     * @throws InformationQueryException for any problem during the query on the information schema
     **/
    public DBInformationContainer getInformation() {

        Connection connection = SingeltonConnection.getConnection();
        DBInformationContainer result = new DBInformationContainer();
        String query = "SELECT TABLE_SCHEMA, TABLE_NAME "
                + "FROM information_schema.TABLES "
                + "ORDER BY TABLE_NAME";

        try (Statement stm = connection.createStatement()) {

            MainLogger.logger.info("Server information request. Query : [" + query + "]");
            ResultSet rs = stm.executeQuery(query);

            while (rs.next()) {
                String schema = rs.getString("TABLE_SCHEMA");
                String table = rs.getString("TABLE_NAME");
                result.addRow(schema, table);
            }

        } catch (SQLException ex) {
            MainLogger.logger.error("Server information query : [" + query + "] - FAILED.", ex);
            throw new InformationQueryException("Information query FAILED", ex);
        }

        return result;
    }

    /**
     * @param dataBase
     * @param table
     * @return ColumnsInformationContainer according to <code>dataBase</code> and <code>table</code>
     * @throws ConnectionException if the connection to the database is not reachable.
     * @throws ColumnsQueryException for any exception during the process.
     */
    public ColumnsInformationContainer getColumnsInformation(String dataBase, String table) {

        Connection connection = SingeltonConnection.getConnection();
        ColumnsInformationContainer result = new ColumnsInformationContainer(dataBase, table);
        String query = "SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, DATA_TYPE "
                + "FROM information_schema.COLUMNS "
                + "WHERE ((TABLE_SCHEMA = '" + dataBase + "') AND (TABLE_NAME = '" + table + "'))";

        try (Statement stm = connection.createStatement()) {

            MainLogger.logger.info("Columns information request. Query : [" + query + "]");
            ResultSet rs = stm.executeQuery(query);

            while (rs.next()) {
                String columnName = rs.getString("COLUMN_NAME");
                String columnType = rs.getString("COLUMN_TYPE");
                String isNullable = rs.getString("IS_NULLABLE");
                String dataType = rs.getString("DATA_TYPE");
                result.addColumn(columnName, columnType, isNullable, dataType);
            }

        } catch (SQLException ex) {
            MainLogger.logger.error("Query : [" + query + "] - FAILED.", ex);
            throw new ColumnsQueryException("Columns information query FAILED", ex);
        }

        return result;
    }
}